package com.epoint.mobileoa.db;



import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

import com.epoint.mobileoa.R;
import com.epoint.mobileoa.model.CodeItemsModel;
import com.epoint.mobileoa.model.Draft;
import com.epoint.mobileoa.model.MailDraft;
import com.epoint.mobileoa.model.Ou;
import com.epoint.mobileoa.model.User;
import com.epoint.mobileoa.tools.DateHelp;
import com.epoint.mobileoa.tools.HanzhisToPinYin;
import com.epoint.mobileoa.tools.LogHelp;

/**
 * @author liyc
 * @time 2012-1-7 下午2:38:57
 * @annotation SQLITE数据库操作
 */
public class DBHelp {

	public static boolean exist(Context con)
	{
		SQLiteDatabase checkDB = null;
		try {
			String allPath = con.getString(R.string.dbpath) + con.getString(R.string.dbname);
			checkDB = SQLiteDatabase.openDatabase(allPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}


	/**
	 * 检查数据库是否存在
	 */
	public static boolean checkDataBase(String dbpath, String dbname) {
		SQLiteDatabase checkDB = null;
		try {
			String allPath = dbpath + dbname;
			checkDB = SQLiteDatabase.openDatabase(allPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {

		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	//设置配置信息
	public static void setConfigValue(Context con,String key,String value){

		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();

		db.execSQL("delete from config where key = '"+key+"'");

		db.execSQL("insert into config values(?,?)",
				new String[]{key,value});

		close(dbhelper, db, null);

	}

	public static void setConfigValueInit(Context con,String key,String value){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("insert into config values(?,?)",
				new String[]{key,value});
		close(dbhelper, db, null);
	}

	//获取配置信息
	public static String getConfigValue(Context con,String key){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select value from config where key='"+key+"'",null);
		String value = "";
		while(cursor.moveToNext()){
			value = cursor.getString(0);
		}
		close(dbhelper, db, cursor);
		return value;
	}

	//关闭数据库操作
	public static void close(DBOpenHelp dbhelper, SQLiteDatabase db,
			Cursor cursor) {
		if (cursor != null) {
			cursor.close();
		}
		if (db != null) {
			db.close();
		}
		if (dbhelper != null) {
			dbhelper.close();
		}
	}

	public static void deleteUsers(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from user");
		close(dbhelper, db, null);
	}

	public static void setUserListPaging(Context con,List<User> ulist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		try{
			db.beginTransaction();   
			for(User u:ulist){
				String szm = HanzhisToPinYin.cn2FirstSpell(u.displayname);
				db.execSQL("insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new String[]{
						u.loginid,u.userguid,u.ouguid,u.displayname,u.mobile,u.telephoneoffice,u.title,
						u.description,u.fax,u.sex,u.email,u.telephonehome,
						u.shortmobile,u.qqnumber,u.ntxextnumber,szm});
			}
			db.setTransactionSuccessful();   
		}
		finally{
			db.endTransaction();   
			close(dbhelper, db, null);
		}

	}

	//同步组织架构保存User
	public static void setUserList(Context con,List<User> ulist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		try{
			db.beginTransaction();   
			db.execSQL("delete from user");
			for(User u:ulist){
				//				LogHelp.LogI(u.displayname);
				//				if(u.displayname==null){
				//					u.displayname = "";
				//				}
				String szm = HanzhisToPinYin.cn2FirstSpell(u.displayname);
				db.execSQL("insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new String[]{
						u.loginid,u.userguid,u.ouguid,u.displayname,u.mobile,u.telephoneoffice,u.title,
						u.description,u.fax,u.sex,u.email,u.telephonehome,
						u.shortmobile,u.qqnumber,u.ntxextnumber,szm});

				/*db.execSQL("insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new String[]{
						u.loginid,u.userguid,u.ouguid,u.displayname,u.mobile,u.telephoneoffice,u.title,
						u.description,u.fax,u.sex,u.email,u.telephonehome,
						u.shortmobile,u.qqnumber,u.ntxextnumber,u.pyshou});*/
			}
			db.setTransactionSuccessful();   
		}
		finally{
			db.endTransaction();   
			close(dbhelper, db, null);
		}
	}

	//宜春同步组织架构保存User
	public static void setUserList_YC(Context con,List<User> ulist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();

		for(User u:ulist){
			//			LogHelp.LogI(u.displayname);
			if(u.displayname==null){
				u.displayname = "";
			}
			String szm = HanzhisToPinYin.cn2FirstSpell(u.displayname);
			db.execSQL("delete from user where userguid=?",new String[]{u.userguid});
			db.execSQL("insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new String[]{
					u.loginid,u.userguid,u.ouguid,u.displayname,u.mobile,u.telephoneoffice,u.title,
					u.description,u.fax,u.sex,u.email,u.telephonehome,
					u.shortmobile,u.qqnumber,u.ntxextnumber,szm});
		}
		close(dbhelper, db, null);
	}

	public static void deleteOus(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from ou");
		close(dbhelper, db, null);
	}

	public static void setOuListPaging(Context con,List<Ou> olist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		try{
			db.beginTransaction();  
			for(Ou o:olist){
				//				LogHelp.LogI(o.ouname);
				//				if(o.ouname==null){
				//					o.ouname = "";
				//				}
				/*String szm = HanzhisToPinYin.cn2FirstSpell(o.ouname);
				db.execSQL("insert into ou values(?,?,?,?)",new String[]{
						o.ouguid,o.ouname,o.parentouguid,szm
				});*/

				db.execSQL("insert into ou values(?,?,?,?)",new String[]{
						o.ouguid,o.ouname,o.parentouguid,""
				});
			}
			db.setTransactionSuccessful();   
		}
		finally{
			db.endTransaction();   
			close(dbhelper, db, null);
		}

	}

	public static void setOuList(Context con,List<Ou> olist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		try{
			db.beginTransaction();  
			db.execSQL("delete from ou");
			for(Ou o:olist){
				//				LogHelp.LogI(o.ouname);
				//				if(o.ouname==null){
				//					o.ouname = "";
				//				}
				/*String szm = HanzhisToPinYin.cn2FirstSpell(o.ouname);
				db.execSQL("insert into ou values(?,?,?,?)",new String[]{
						o.ouguid,o.ouname,o.parentouguid,szm
				});*/

				db.execSQL("insert into ou values(?,?,?,?)",new String[]{
						o.ouguid,o.ouname,o.parentouguid,""
				});
			}
			db.setTransactionSuccessful();   
		}
		finally{
			db.endTransaction();   
			close(dbhelper, db, null);
		}

	}

	//	public static void setUserFromJson(Context con,String jstr){
	//		Type listType = new TypeToken<LinkedList<User>>(){}.getType();
	//		Gson gson = new Gson();
	//		LinkedList<User> user = gson.fromJson(jstr, listType);
	//		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname")));
	//		SQLiteDatabase db = dbhelper.getWritableDatabase();
	//		db.execSQL("delete from user");
	//		for(Iterator iterator=user.iterator();iterator.hasNext();){
	//			User u = (User) iterator.next();
	//
	//			LogHelp.LogI(u.displayname);
	//			if(u.displayname==null){
	//				u.displayname = "";
	//			}
	//			String szm = HanzhisToPinYin.cn2FirstSpell(u.displayname);
	//
	//			db.execSQL("insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
	//					new String[]{
	//					u.loginid,u.userguid,u.ouguid,u.displayname,u.mobile,
	//					u.telephoneoffice,u.title,u.description,u.fax,u.sex,
	//					u.email,u.telephonehome,u.shortmobile,u.qqnumber,u.ntxextnumber,
	//					szm
	//			});
	//		}
	//		close(dbhelper, db, null);
	//	}

	public static List<Map<String, Object>> getRootDeptMapReturn(Context con,int isOrderByPY,String parentguid) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		db.setLocale(Locale.CHINA);
		Cursor cursor = null;
		if(isOrderByPY==1){
			cursor = db.rawQuery(
					"select ouname ,ouguid from ou where parentguid = ?",
					new String[] { parentguid });// order by ounamepyshou asc
		}
		else{
			cursor = db.rawQuery("select ouname,ouguid from ou where parentguid = ? ",//order by ouname
					new String[] { parentguid });
		}
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Map<String, Object> map;
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			map.put("title", cursor.getString(0));// 部门名称
			map.put("guid", cursor.getString(1));// 部门编号
			map.put("info", "部门");
			map.put("dp", "部门");
			map.put("name", cursor.getString(0));
			list.add(map);
		}

		if(isOrderByPY==1){
			cursor = db.rawQuery(
					"select displayname,userguid ,loginid,sex,ouname,title from user ,ou where  user.ouguid = ou.ouguid and user.ouguid =?",// order by namepyshou asc
					new String[] { parentguid });
		}
		else{
			cursor = db.rawQuery(
					"select displayname,userguid,loginid,sex,ouname,title,mobile from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and LOWER(user.ouguid) =LOWER(?)",// order by displayname
					new String[] { parentguid });
		}
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			String title = cursor.getString(0);
			String mobile = cursor.getString(6)==null?"":cursor.getString(6);
			if(!mobile.equals("")){
				title += ":"+mobile;
			}
			map.put("title", title);// 人员名称
			map.put("name", cursor.getString(0));
			map.put("guid", cursor.getString(1));// 人员编号
			map.put("username", cursor.getString(2));
			map.put("info", "人员");
			String dp = cursor.getString(4);
			if(cursor.getString(5)!=null&&!cursor.getString(5).equals("")){
				dp += ":"+cursor.getString(5);
			}
			map.put("mobile", mobile);
			map.put("dp", dp);
			list.add(map);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static List<Map<String, Object>> getRootDeptMapReturnForPersonChoose(Context con,int isOrderByPY,String parentguid) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		db.setLocale(Locale.CHINA);
		Cursor cursor = null;
		if(isOrderByPY==1){
			cursor = db.rawQuery(
					"select ouname ,ouguid from ou where parentguid = ?",
					new String[] { parentguid });// order by ounamepyshou asc
		}
		else{
			cursor = db.rawQuery("select ouname,ouguid from ou where parentguid = ?",
					new String[] { parentguid });// order by ouname
		}
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Map<String, Object> map;
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			map.put("title", cursor.getString(0));// 部门名称
			map.put("guid", cursor.getString(1));// 部门编号
			map.put("info", "部门");
			map.put("dp", "部门");
			map.put("name", cursor.getString(0));
			list.add(map);
		}

		if(isOrderByPY==1){
			cursor = db.rawQuery(
					"select displayname,userguid ,loginid,sex,ouname,title from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and LOWER(user.ouguid) =?",// order by namepyshou asc
					new String[] { parentguid });
		}
		else{
			cursor = db.rawQuery(
					"select displayname,userguid,loginid,sex,ouname,title,mobile from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and LOWER(user.ouguid) =?",// order by displayname
					new String[] { parentguid });
		}
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			String title = cursor.getString(0);
			String mobile = cursor.getString(6)==null?"":cursor.getString(6);
			map.put("title", title);// 人员名称
			map.put("name", cursor.getString(0));
			map.put("guid", cursor.getString(1));// 人员编号
			map.put("username", cursor.getString(2));
			map.put("info", "人员");
			String dp = cursor.getString(4);
			if(cursor.getString(5)!=null&&!cursor.getString(5).equals("")){
				dp += ":"+cursor.getString(5);
			}
			map.put("mobile", mobile);
			map.put("dp", dp);
			list.add(map);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static List<Map<String, Object>> getUserOnPy(Context con,String py) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select displayname ,userguid ,loginid,sex,ou.ouname,title,mobile from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and (user.namepyshou like '%"+py+"%' or user.displayname like '%"+py+"%')order by user.namepyshou asc",
				null);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Map<String, Object> map;
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			String title = cursor.getString(0);
			String mobile = cursor.getString(6)==null?"":cursor.getString(6);
			if(!mobile.equals("")){
				title += ":"+mobile;
			}
			map.put("title", title);// 人员名称
			map.put("name", cursor.getString(0));
			map.put("mobile", mobile);
			map.put("guid", cursor.getString(1));// 人员编号
			map.put("username", cursor.getString(2));
			map.put("info", "人员");
			String dp = cursor.getString(4);
			if(cursor.getString(5)!=null&&!cursor.getString(5).equals("")){
				dp += ":"+cursor.getString(5);
			}
			map.put("dp", dp);
			list.add(map);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static List<Map<String, Object>> getUserOnPyForPersonChoose(Context con,String py) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select displayname ,userguid ,loginid,sex,ou.ouname,title,mobile from user ,ou where  user.ouguid = ou.ouguid and user.namepyshou like '"+py+"%' order by user.namepyshou asc",
				null);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Map<String, Object> map;
		while (cursor.moveToNext()) {
			map = new HashMap<String, Object>();
			String title = cursor.getString(0);
			String mobile = cursor.getString(6)==null?"":cursor.getString(6);
			map.put("title", title);// 人员名称
			map.put("name", cursor.getString(0));
			map.put("mobile", mobile);
			map.put("guid", cursor.getString(1));// 人员编号
			map.put("username", cursor.getString(2));
			map.put("info", "人员");
			String dp = cursor.getString(4);
			if(cursor.getString(5)!=null&&!cursor.getString(5).equals("")){
				dp += ":"+cursor.getString(5);
			}
			map.put("dp", dp);
			list.add(map);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static Map<String, String> getUserDetailInfo(Context con,String userguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("Select * from  user ,ou where LOWER(user.ouguid) = LOWER(ou.ouguid) and userguid = ?",
				new String[] { userguid });
		Map<String, String> map = new HashMap<String, String>();
		while (cursor.moveToNext()) {
			map.put("name", cursor.getString(cursor.getColumnIndex("displayname"))==null?"":cursor.getString(cursor.getColumnIndex("displayname")));
			map.put("sex", cursor.getString(cursor.getColumnIndex("sex"))==null?"":cursor.getString(cursor.getColumnIndex("sex")));
			map.put("mobilephone", cursor.getString(cursor.getColumnIndex("mobile"))==null?"":cursor.getString(cursor.getColumnIndex("mobile")));
			map.put("officephone", cursor.getString(cursor.getColumnIndex("telephoneoffice"))==null?"":cursor.getString(cursor.getColumnIndex("telephoneoffice")));
			map.put("email", cursor.getString(cursor.getColumnIndex("email"))==null?"":cursor.getString(cursor.getColumnIndex("email")));
			map.put("deptname", cursor.getString(cursor.getColumnIndex("ouname"))==null?"":cursor.getString(cursor.getColumnIndex("ouname")));
			map.put("title", cursor.getString(cursor.getColumnIndex("title"))==null?"":cursor.getString(cursor.getColumnIndex("title")));
			map.put("remark", cursor.getString(cursor.getColumnIndex("description"))==null?"":cursor.getString(cursor.getColumnIndex("description")));
			map.put("telephonehome", cursor.getString(cursor.getColumnIndex("telephonehome"))==null?"":cursor.getString(cursor.getColumnIndex("telephonehome")));
			map.put("shortmobile", cursor.getString(cursor.getColumnIndex("shortmobile"))==null?"":cursor.getString(cursor.getColumnIndex("shortmobile")));
			map.put("qqnumber", cursor.getString(cursor.getColumnIndex("qqnumber"))==null?"":cursor.getString(cursor.getColumnIndex("qqnumber")));
			map.put("ntxextnumber", cursor.getString(cursor.getColumnIndex("ntxextnumber"))==null?"":cursor.getString(cursor.getColumnIndex("ntxextnumber")));
		}
		close(dbhelper, db, cursor);
		return map;
	}

	public static void updateUser(Context con,String userguid,Map<String,String> map){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("update user set sex=?,title=?,telephoneoffice=?,ntxextnumber=?,shortmobile=?,telephonehome=?,qqnumber=?," +
				"email=?,description=?,displayname=?,mobile=? where userguid = ?",new String[]{
				map.get("sex"),
				map.get("title"),
				map.get("telephoneoffice"),
				map.get("ntxextnumber"),
				map.get("shortmobile"),
				map.get("telephonehome"),
				map.get("qqnumber"),
				map.get("email"),
				map.get("description"),
				map.get("displayname"),
				map.get("mobile"),
				userguid
		});
		close(dbhelper, db, null);
	}

	/**
	 * 获取草稿箱详情
	 * @param con
	 * @param dbname
	 * @param username
	 * @return
	 */
	public static List<MailDraft> getMailDraftDetail(Context con, String dbname,
			String username) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, dbname);
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from maildraft where senderguid = ? order by draftsavetime desc",
				new String[] { username });
		List<MailDraft> list = new ArrayList<MailDraft>();
		while (cursor.moveToNext()) {
			MailDraft msb = new MailDraft();
			msb.draftguid = cursor.getString(cursor.getColumnIndex("draftguid"));
			msb.mailtitle = cursor.getString(cursor.getColumnIndex("mailtitle"));
			msb.draftsavetime = cursor.getString(cursor.getColumnIndex("draftsavetime"));
			msb.sendername = cursor.getString(cursor.getColumnIndex("sendername"));
			msb.senderguid =  cursor.getString(cursor.getColumnIndex("senderguid"));
			msb.receivername = cursor.getString(cursor.getColumnIndex("receivername"));
			msb.receiveguid = cursor.getString(cursor.getColumnIndex("receiveguid"));
			msb.chaosongrename = cursor.getString(cursor.getColumnIndex("chaosongrename"));
			msb.chaosongrenguid = cursor.getString(cursor.getColumnIndex("chaosongrenguid"));
			msb.mailcontent = cursor.getString(cursor.getColumnIndex("mailcontent"));
			msb.attachname = cursor.getString(cursor.getColumnIndex("attachname"));
			msb.attachpath = cursor.getString(cursor.getColumnIndex("attachpath"));
			list.add(msb);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static int getUserNums(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor =db.rawQuery("select count(*) from user", null);
		int nums = 0;
		while(cursor.moveToNext()){
			nums = cursor.getInt(0);
		}
		close(dbhelper, db, cursor);
		return nums;
	}

	//保存二进制数据
	public static void setRes(Context con,String key,byte[] value){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from res where key = ?",new String[]{key});
		ContentValues cv = new ContentValues();
		cv.put("key", key);
		cv.put("value", value);
		db.insert("res", null, cv);
		close(dbhelper, db, null);
	}

	//获取二进制数据
	public static byte[] getRes(Context con,String key){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();

		Cursor cursor = db.rawQuery("select value from res where key = ?",new String[]{key});
		byte[] odata = null;
		while(cursor.moveToNext()){
			odata = cursor.getBlob(0);
		}
		close(dbhelper, db, cursor);
		return odata;
	}

	//获取一个部门下面的所有用户,进此部门根下面的人员
	public static List<Map<String, String>> getUnderDeptPersonInfo(Context con,String ouguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select userguid,displayname from user where ouguid = '"+ouguid+"'", null);
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		while(cursor.moveToNext()){
			Map<String,String> map = new HashMap<String, String>();
			map.put("userguid", cursor.getString(0));
			map.put("name", cursor.getString(1));
			list.add(map);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	/**
	 * liyc
	 * 获取一个部门下面所有的人员，包括子部门
	 */
	public static List<Object> getAllUserUnderOu(Context con,Map<String,String> oumap){
		List<Map<String,String>> oulist = new ArrayList<Map<String,String>>();
		oulist.add(oumap);
		getAllOuUnderParent(con,oumap.get("guid"),oulist);
		List<Map<String,String>> userlist = new ArrayList<Map<String,String>>();
		for(Map<String,String> m1:oulist){
			List<Map<String, String>> singleuserlist = getUnderDeptPersonInfo(con,m1.get("guid"));
			for(Map<String, String> m:singleuserlist){
				userlist.add(m);
			}
		}
		List<Object> objectlist = new ArrayList<Object>();
		objectlist.add(oulist);
		objectlist.add(userlist);
		return objectlist;
	}

	public static void getAllOuUnderParent(Context con,String ouguid,List<Map<String, String>> oulist){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select ouguid,ouname from ou where parentguid = '"+ouguid+"'", null);

		if(cursor.getCount()==0){

		}
		else{
			while(cursor.moveToNext()){
				String couguid = cursor.getString(0);
				String couname = cursor.getString(1);
				Map<String,String> map = new HashMap<String, String>();
				map.put("guid", couguid);
				map.put("name", couname);
				if(!oulist.contains(map)){
					oulist.add(map);
				}
				getAllOuUnderParent(con,couguid,oulist);
			}
		}
		close(dbhelper, db, cursor);
	}

	public static void setDraft(Context con,Draft df){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from maildraft where draftguid = ?",new String[]{df.draftguid});
		db.execSQL("insert into maildraft values(?,?,?,?,?,?,?,?,?,?,?,?,?)",new String[]{
				df.draftguid,
				df.mailtitle,
				df.draftsavetime,
				df.sendername,
				df.senderguid,
				df.receivername,
				df.receiveguid,
				df.chaosongrename,
				df.chaosongrenguid,
				df.mailcontent,
				df.hasattach,
				df.attachname,
				df.attachpath
		});
		close(dbhelper, db, null);
	}

	public static List<Draft> getDraftList(Context con,
			String userguid) {
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from maildraft where senderguid = ? order by draftsavetime desc",
				new String[] {userguid});
		List<Draft> list = new ArrayList<Draft>();
		while(cursor.moveToNext()){
			Draft df = new Draft();
			df.draftguid = cursor.getString(0);
			df.mailtitle = cursor.getString(1);
			df.draftsavetime= cursor.getString(2);
			df.sendername= cursor.getString(3);
			df.senderguid= cursor.getString(4);
			df.receivername= cursor.getString(5);
			df.receiveguid= cursor.getString(6);
			df.chaosongrename= cursor.getString(7);
			df.chaosongrenguid= cursor.getString(8);
			df.mailcontent= cursor.getString(9);
			df.hasattach= cursor.getString(10);
			df.attachname= cursor.getString(11);
			df.attachpath= cursor.getString(12);
			list.add(df);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static String getUserOuguid(Context con,
			String userguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select ouguid from user where userguid = ?",
				new String[] {userguid});
		String ouguid = "";
		while(cursor.moveToNext()){
			ouguid = cursor.getString(0);
		}
		close(dbhelper, db, cursor);
		return ouguid;
	}

	public static String getUserOuname(Context con,
			String userguid){
		String ouguid = getUserOuguid(con,userguid);
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select ouname from ou where ouguid = ?",
				new String[] {ouguid});
		String ouname = "";
		while(cursor.moveToNext()){
			ouname = cursor.getString(0);
		}
		close(dbhelper, db, cursor);
		return ouname;
	}

	public static String getUserBaseOuguid(Context con,
			String userguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select ouguid from user where userguid = ?",
				new String[] {userguid});
		String ouguid = "";
		while(cursor.moveToNext()){
			ouguid = cursor.getString(0);
		}
		close(dbhelper, db, cursor);
		return ouguid;
	}

	public static String getRootOuGuid(Context con,
			String userguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select ouguid from user where userguid = ?",
				new String[] {userguid});
		String ouguid = "";
		while(cursor.moveToNext()){
			ouguid = cursor.getString(0);
		}

		ouguid = ouguid==null?"":ouguid;
		close(dbhelper, db, cursor);
		if(ouguid.equals("")){
			return ouguid;
		}
		else{
			getRootOuGuidByOuguid(con,ouguid);
			return ouguidtemp;
		}
	}

	private static String ouguidtemp;
	public static void getRootOuGuidByOuguid(Context con,
			String ouguid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select parentguid from ou where ouguid = ?",
				new String[] {ouguid});
		String parentguid = "";
		while(cursor.moveToNext()){
			parentguid = cursor.getString(0);
		}
		parentguid = parentguid==null?"":parentguid;
		if(parentguid.equals("")){
			ouguidtemp = ouguid;
		}
		else{
			getRootOuGuidByOuguid(con,parentguid);
		}
		close(dbhelper, db, cursor);
	}

	public static int getOuCount(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor =db.rawQuery("select count(*) from ou", null);
		int nums = 0;
		while(cursor.moveToNext()){
			nums = cursor.getInt(0);
		}
		close(dbhelper, db, cursor);
		return nums;
	}

	//更新历史记录表
	public static void updateLoginHistory(Context con,String loginid){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from login_history where loginid=?",new String[]{loginid});
		String date = DateHelp.convertDate(new Date(), "yyyy-MM-dd hh:mm:ss");
		db.execSQL("insert into login_history values(?,?)",new String[]{loginid,date});
		close(dbhelper, db, null);
	}

	//获取历史记录表信息
	public static String[] getLoginHistoryInfo(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		Cursor cursor =db.rawQuery("select loginid from login_history", null);
		int nums = cursor.getCount();
		String[] res = new String[nums];

		int i = 0;
		while(cursor.moveToNext()){
			res[i] = cursor.getString(0);
			i++;
		}
		close(dbhelper, db, cursor);
		return res;

	}

	//保存CodeItems
	public static void saveCode_Items(Context con,List<CodeItemsModel> list){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		String codename = list.get(0).CodeName;
		db.execSQL("delete from code_items where CodeName = '"+codename+"'");
		try{
			db.beginTransaction();   
			for(CodeItemsModel cim:list){
				db.execSQL("insert into code_items values(?,?,?,?,?)",new String[]{cim.ItemValue,cim.ItemText,cim.ItemID,cim.CodeID,cim.CodeName});
			}
			db.setTransactionSuccessful();   

		}finally{
			db.endTransaction();   
			close(dbhelper, db, null);
		}
	}

	public static List<CodeItemsModel> getChildCodeItems(Context con,String ItemName,String parentitemvalue,String style){

		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();

		String sql = "SELECT * FROM code_items where CodeName = '"+ItemName+"' ";

		if(style!=null){
			sql += "and itemvalue like '"+style+"%'";
		}

		sql += "and itemvalue like '"+parentitemvalue+"%' and length(itemvalue) = "+(parentitemvalue.length()+2)+"";
		Cursor cursor =db.rawQuery(sql, null);
		//		Cursor cursor =db.rawQuery("SELECT * FROM code_items where CodeName = '"+ItemName+"' and itemvalue like '"+parentitemvalue+"%' and itemvalue like '"+style+"%' and length(itemvalue) = "+(parentitemvalue.length()+2)+"", null);
		List<CodeItemsModel> list = new ArrayList<CodeItemsModel>();

		while(cursor.moveToNext()){
			CodeItemsModel cim = new CodeItemsModel();
			cim.ItemValue = cursor.getString(0);
			cim.ItemText = cursor.getString(1);
			cim.ItemID = cursor.getString(2);
			cim.CodeID = cursor.getString(3);
			cim.CodeName = cursor.getString(4);
			list.add(cim);
		}

		DBHelp.close(dbhelper, db, cursor);

		return list;
	}

	public static String getItemValue(Context con,String ItemText,String CodeName){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		Cursor cursor =db.rawQuery("SELECT itemvalue FROM code_items where ItemText = ? and CodeName = ?",
				new String[]{ItemText,CodeName});
		String itemvalue = "";
		while(cursor.moveToNext()){
			itemvalue = cursor.getString(0);
		}
		close(dbhelper, db, cursor);
		return itemvalue;
	}

	//获取所有部门
	public static List<Ou> getAllOuList(Context con){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		List<Ou> list = new ArrayList<Ou>();
		Cursor cursor =db.rawQuery("SELECT * from ou",
				null);
		while(cursor.moveToNext()){
			Ou o = new Ou();
			o.ouguid = cursor.getString(0);
			o.ouname = cursor.getString(1);
			o.parentouguid = cursor.getString(2);
			list.add(o);
		}
		close(dbhelper, db, cursor);
		return list;
	}

	public static byte[] getResData(Context con,String key){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();

		Cursor cursor = db.rawQuery("select value from res where key = ?", new String[]{key});

		byte[] odata = null;

		while(cursor.moveToNext()){
			odata = cursor.getBlob(0);
		}

		close(dbhelper, db, cursor);
		return odata;
	}

	/*
	 * 保存二进制数据
	 */
	public static void saveResData(Context con,
			String key, byte[] odata){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from res where key = ?",new String[]{key});
		ContentValues cv = new ContentValues();
		cv.put("key", key);
		cv.put("value", odata);
		db.insert("res", null, cv);
		close(dbhelper, db, null);
	}

	/*
	 * 删除二进制数据
	 */
	public static void deleteResData(Context con,
			String key){
		DBOpenHelp dbhelper = new DBOpenHelp(con, con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getWritableDatabase();
		db.execSQL("delete from res where key = ?",new String[]{key});
		close(dbhelper, db, null);
	}

	/**
	 * 根据guid，以分号隔开获取人员名称 liyc
	 * 
	 */
	public static String getUserDisplayNamesByGuids(Context con, String guids) {
		DBOpenHelp dbhelper = new DBOpenHelp(con,
				con.getString(R.string.dbname));
		SQLiteDatabase db = dbhelper.getReadableDatabase();
		Cursor cursor = null;
		String names = "";
		String[] guidArr = guids.split(";");
		for (String guid : guidArr) {
			if (!guid.equals("")) {
				cursor = db.rawQuery(
						"select displayname from user where userguid = ?",
						new String[] { guid });
				while (cursor.moveToNext()) {
					names += cursor.getString(0) + ";";
				}
			}
		}
		return names;
	}

}
